package hw.pjbk;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper {

	static final String dbName = "quiz";

	// question table and fields

	public DatabaseHelper(Context context) {
		super(context, dbName, null, 6);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		// create the questions table
		db.execSQL("CREATE TABLE Questions "
				+ " (QuestionID INTEGER PRIMARY KEY  NOT NULL, "
				+ "QuestionText VARCHAR, "
				+ "CorrectAnswerID INTEGER DEFAULT (null));");
		// add some questions
		db.execSQL("INSERT INTO Questions SELECT 1,'Who was the first British prime minister after World War II?',3 "
				+ " UNION SELECT 2,'What year did the English Civil War end?',8 "
				+ " UNION SELECT 3,'Which country gained independence form the British Empire in 1947?',10 "
				+ " UNION SELECT 4,'In what year was the dissolution of the Soviet Union?',16;");

		// create the answers table
		db.execSQL("CREATE TABLE Answers ("
				+ "'AnswerID' INTEGER PRIMARY KEY  NOT NULL  DEFAULT (null) ,"
				+ "'AnswerText' VARCHAR, 'QuesstionID' INTEGER);");

		// add some answers
		db.execSQL("INSERT INTO 'Answers' SELECT 1,'Neville Chamberlain',1 "
				+ " UNION SELECT 2,'Winston Churchill',1 "
				+ " UNION SELECT 3,'Clement Attlee',1 "
				+ " UNION SELECT 4,'Anthony Eden',1 "
				+ " UNION SELECT 5,'1626',2 " + " UNION SELECT 6,'1701',2 "
				+ " UNION SELECT 7,'1642',2 " + " UNION SELECT 8,'1646',2 "
				+ " UNION SELECT 9,'Cameroon',3 " + " UNION SELECT 10,'India',3 "
				+ " UNION SELECT 11,'Rhodesia',3 "
				+ " UNION SELECT 12,'South Africa',3 "
				+ " UNION SELECT 13,'1985',4 " + " UNION SELECT 14,'1997',4 "
				+ " UNION SELECT 15,'1973',4 " + " UNION SELECT 16,'1991',4;");

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS Questions");
		db.execSQL("DROP TABLE IF EXISTS Answers");
		onCreate(db);

	}

	// get all possible answers for the question specified by its ID
	Cursor getAnswers(String QuestionID) {

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("SELECT AnswerID as _id, AnswerText "
				+ " from Answers WHERE QuesstionID = ?",
				new String[] { QuestionID });

		return cur;

	}

	// get all questions
	Cursor getQuestions() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db
				.rawQuery(
						"SELECT QuestionID AS _id, QuestionText, CorrectAnswerID FROM Questions;",
						null);

		return cur;

	}

}
